<?php
class bc_mdl extends CI_Model
{

	
	public function get_all($limit, $start)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
fournisseurs.id_fournisseur,
bcs.id_bc,
bcs.date,
bcs.montant,
bcs.id_fournisseur_fk,
bcs.numero,
engagements.numero AS num_engagement,
mandats.numero AS num_mondat,
articles.id_article,
articles.num_article
FROM
fournisseurs
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
LEFT JOIN mandats ON bcs.id_mondat_fk = mandats.id_mandat
INNER JOIN articles ON bcs.id_article_fk = articles.id_article 
WHERE bcs.annee = $year
GROUP BY bcs.id_bc
LIMIT  $start ,$limit

 ");
	
return $query->result();
     

	
	}
	
	public function get_all_per_nom()
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom ,
fourn_article.id_fournisseur_fk,
fourn_article.id_article_fk,
fournisseurs.id_fournisseur,
articles.nom AS article,
bcs.id_bc,
bcs.montant,
bcs.date
FROM
fournisseurs
INNER JOIN fourn_article ON fourn_article.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON fourn_article.id_article_fk = articles.id_article
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE bcs.annee = $year
GROUP BY
fournisseurs.id_fournisseur

 ");
	
return $query->result();
     

	
	}
	
	public function get_per_nom_fourn($nom)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
fournisseurs.id_fournisseur,
bcs.id_bc,
bcs.date,
bcs.montant,
bcs.id_fournisseur_fk,
bcs.numero,
engagements.numero AS num_engagement,
mandats.numero AS num_mondat,
articles.id_article,
articles.num_article
FROM
fournisseurs
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
LEFT JOIN mandats ON bcs.id_mondat_fk = mandats.id_mandat
INNER JOIN articles ON bcs.id_article_fk = articles.id_article 
WHERE
fournisseurs.nom  like '%$nom%' AND
bcs.annee = $year
 ");
	
return $query->result();

	
	}
	
		public function count_per_nom_fourn($nom)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
fourn_article.id_fournisseur_fk,
fourn_article.id_article_fk,
fournisseurs.id_fournisseur,
articles.nom AS article,
bcs.id_bc,
bcs.date
FROM
fournisseurs
INNER JOIN fourn_article ON fourn_article.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON fourn_article.id_article_fk = articles.id_article
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
fournisseurs.nom  like '%$nom%' AND
bcs.annee = $year
 ");
$cont=$query->num_rows();
return $cont;
	}
		public function get_all2($limit, $start,$xx)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
fournisseurs.id_fournisseur,
bcs.id_bc,
bcs.date,
bcs.montant,
bcs.id_fournisseur_fk,
bcs.numero,
engagements.numero AS num_engagement,
mandats.numero AS num_mondat,
articles.id_article,
articles.num_article,
articles.nom AS article
FROM
fournisseurs
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
LEFT JOIN mandats ON bcs.id_mondat_fk = mandats.id_mandat
INNER JOIN articles ON bcs.id_article_fk = articles.id_article

WHERE
articles.id_article = $xx AND
bcs.annee = $year
LIMIT  $start ,$limit

 ");
	
return $query->result();

	}
	
	public function get_all_recherche($xx)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
bcs.id_bc,
bcs.date,
bcs.montant,
fournisseurs.nom ,
fourn_article.id_article_fk,
fourn_article.id_fournisseur_fk,
articles.nom AS article,
articles.id_article
FROM
bcs
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN fourn_article ON fourn_article.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON fourn_article.id_article_fk = articles.id_article
WHERE
articles.id_article = '$xx' AND
bcs.annee = $year

 ");
	
return $query->result();

	}

   public function get_wilaya()
	{


	$query = $this->db->query("SELECT
wilayas.id_wilaya,
wilayas.nom_fr,
wilayas.nom_ar
FROM
wilayas

 ");
	
return $query->result();

	}
	
	 public function record_count_all() {

$this->load->library('session');
$year=$this->session->userdata('annee');
	  	
      $q = $this->db->query("SELECT
bcs.id_bc,
bcs.date,
fournisseurs.nom
FROM
bcs
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE bcs.annee = $year


");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count($articl_id) {

$this->load->library('session');
$year=$this->session->userdata('annee');
	  	
      $q = $this->db->query("SELECT
bcs.id_bc,
bcs.date,
fournisseurs.nom,
articles.nom
FROM
bcs
INNER JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE bcs.annee = $year AND
bcs.id_bc = $articl_id
");

$cont=$q->num_rows();
return $cont;

    }
	
	
	public function get_detaille($xx)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
bcs.id_bc,
bcs.date,
bc_produits.id_bc_produit,
bc_produits.quantite,
bc_produits.bc_produit_pu,
produits.dessignation,
bcs.numero,
bcs.id_mondat_fk,
bcs.id_engagement_fk,
engagements.numero AS num_engagement,
bcs.id_article_fk,
bcs.annee,
bcs.tva,
bcs.montant,
fournisseurs.id_fournisseur,
fournisseurs.nom,
fournisseurs.adresse,
activite.titre
FROM
bcs
LEFT JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
LEFT JOIN produits ON bc_produits.id_produit_fk = produits.id_produit
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN activite ON fournisseurs.id_activite_fk = activite.id_activite
WHERE
bcs.id_bc = $xx AND
bcs.annee = $year

 ");
 if($query->num_rows==0){ // si le bc ne contien pas de produit
 	 
  $query = $this->db->query("SELECT
bcs.id_bc,
bcs.numero,
bcs.date,
fournisseurs.nom,
fournisseurs.id_fournisseur
FROM
bcs
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
bcs.id_bc = $xx
 ");
 }
return $query->result();

	}
    
	public function get_detaille_mini($id_bc)
	{

	$query = $this->db->query("SELECT
bcs.id_bc,
bcs.id_article_fk,
bcs.montant,
bcs.id_fournisseur_fk
FROM
bcs
WHERE
bcs.id_bc = $id_bc


 ");
 
return $query->result();

	}
	
    public function get_bc_produit($xx)
	{

	$query = $this->db->query("SELECT
bc_produits.quantite,
bc_produits.bc_produit_pu,
bcs.id_bc,
bcs.tva
FROM
bcs
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
WHERE
bcs.id_bc = $xx

 ");

return $query->result();

	}
	
	public function ajout_bc($id_fournisseur,$id_engagement,$id_article,$date,$numero,$tva)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

    if(!$id_engagement){
		$query = $this->db->query("
	INSERT INTO `bcs` (`id_fournisseur_fk`,`date`,`annee`,`id_article_fk` ,`numero`,`tva`) VALUES ('$id_fournisseur','$date','$year','$id_article','$numero','$tva') ");
	}else{
		$query = $this->db->query("
	INSERT INTO `bcs` (`date`, `id_fournisseur_fk`, `id_engagement_fk`,`id_article_fk` ,`annee`,`numero`,`tva`) VALUES ( '$date', '$id_fournisseur', '$id_engagement','$id_article','$year','$numero','$tva') ");
	}
	

	


	}
	
	public function verifie_seuill_regulier($id_fournisseur,$id_article)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("
	SELECT
bc_produits.quantite,
bc_produits.bc_produit_pu,
fournisseurs.id_fournisseur,
fournisseurs.date_regule,
bcs.id_bc,
bcs.date,
fournisseurs.nom,
bcs.tva
FROM bcs
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
WHERE
fournisseurs.id_fournisseur = $id_fournisseur AND
bcs.id_convention IS NULL AND
bcs.annee = $year AND
bcs.id_engagement_fk IS NOT NULL AND
bcs.id_article_fk = $id_article


 ");
	
return $query->result();

	}   
	
	public function get_bc_per_fourn($id_fournisseur)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("
	SELECT bcs.id_bc
    FROM bcs
    WHERE
    bcs.id_fournisseur_fk = $id_fournisseur AND
	bcs.annee = $year
 ");
	
return $query->result();

	}

    public function get_bc_pour_regule($id_fournisseur,$id_art)
	{

$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("
	SELECT bcs.id_bc
FROM bcs
WHERE
bcs.id_fournisseur_fk = $id_fournisseur AND
bcs.annee = $year AND
bcs.id_convention IS NULL AND
bcs.id_article_fk = $id_art
 ");
	
return $query->result();

	}
	
	public function regularise_bc($id_bc,$id_convention)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("
	UPDATE `bcs` SET `id_convention`='$id_convention' WHERE (`id_bc`='$id_bc')
 ");

	}
  
   public function get_bc_nouveau_id()
	{

	$query = $this->db->query("
	SELECT DISTINCT
	bcs.id_bc,
	bcs.numero
FROM
	bcs
ORDER BY
	bcs.id_bc DESC
LIMIT 0,1
 ");
	
return $query->result();

	}

   public function engager_bc($id_bc,$id_engagement)
	{

	$query = $this->db->query("UPDATE `bcs` SET `id_engagement_fk`='$id_engagement' WHERE (`id_bc`='$id_bc')
 ");
	

	}

    public function verifie_numero($numero)
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
bcs.id_bc,
bcs.numero
FROM
bcs
WHERE
bcs.numero = $numero AND
bcs.annee = $year

 ");
 return $query->result();

	}
	
	 public function get_numero()
	{
$this->load->library('session');
$year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
bcs.id_bc,
bcs.numero
FROM
bcs
WHERE
bcs.annee = $year
ORDER BY
bcs.numero DESC
LIMIT 0, 1

 ");
 return $query->result();	

	}

     public function set_montant($id_bc,$montant)
	{

	$query = $this->db->query("UPDATE `bcs` SET `montant`='$montant' WHERE (`id_bc`='$id_bc')
 ");
 

	}

    public function payer_bc($id_bc,$num_cheque,$num_facture,$date,$numero_mondat,$montant)
	{
		$this->load->library('session');
$year=$this->session->userdata('annee');
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	INSERT INTO `mandats` (`numero_cheque`, `id_methode_pay_fk`, `date`, `montant`, `numero`, `annee`) VALUES ('$num_cheque', '1', '$date', '$montant', '$numero_mondat','$year')
 ");
   // on ajout le bc a cette mondat
   $q=$this->db->query("SELECT mandats.id_mandat
FROM
mandats
WHERE
mandats.annee = $year
ORDER BY
mandats.id_mandat DESC
LIMIT 0, 1
");
   // et on recupere les detaille
   
	
return $q->result();

	}

     public function attacher_bc_mondat($id_bc,$id_mondat)
	{
	
   // on va inserer une nouvelle mondat_ctr
   $query = $this->db->query("
	UPDATE `bcs` SET `id_mondat_fk`='$id_mondat' WHERE (`id_bc`='$id_bc')");
 
	}

     public function existe_bc($numero,$id_fourn)
	{
	$this->load->library('session');
$year=$this->session->userdata('annee');
 
   $query = $this->db->query("
	SELECT bcs.id_bc
FROM bcs
WHERE
bcs.numero = $numero AND
bcs.annee = $year AND
bcs.id_fournisseur_fk = $id_fourn
");

 return $query->result();
	}

     public function supprimer_bc($id_bc)
	{
	
   $query = $this->db->query("
	DELETE FROM `bcs` WHERE (`id_bc`='$id_bc')
");

 
	}

     public function get_per_date($j1,$j2,$m1,$m2)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
fournisseurs.id_fournisseur,
bcs.id_bc,
bcs.date,
bcs.montant,
bcs.id_fournisseur_fk,
bcs.numero,
engagements.numero AS num_engagement,
mandats.numero AS num_mondat,
articles.id_article,
articles.num_article
FROM
fournisseurs
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
LEFT JOIN mandats ON bcs.id_mondat_fk = mandats.id_mandat
INNER JOIN articles ON bcs.id_article_fk = articles.id_article
WHERE
bcs.date > '$year-$m1-$j1' AND
bcs.date < '$year-$m2-$j2' AND
bcs.annee = $year


 ");

return $query->result();
     

	
	}

     public function get_per_wilaya($num_wilaya)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 
	$query = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
fournisseurs.id_fournisseur,
bcs.id_bc,
bcs.montant,
bcs.date,
bcs.id_fournisseur_fk,
bcs.numero,
engagements.numero AS num_engagement,
mandats.numero AS num_mondat,
articles.id_article,
articles.num_article,
wilayas.nom_ar
FROM
fournisseurs
INNER JOIN bcs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN engagements ON bcs.id_engagement_fk = engagements.id_engagement
LEFT JOIN mandats ON bcs.id_mondat_fk = mandats.id_mandat
INNER JOIN articles ON bcs.id_article_fk = articles.id_article
LEFT JOIN wilayas ON fournisseurs.id_wilaya_fk = wilayas.id_wilaya
WHERE
wilayas.nom_ar like '%$num_wilaya%' AND
bcs.annee = '$year'


 ");

return $query->result();
     

	
	}

      public function get_bc($id_fourn,$numero)
	{


	$query = $this->db->query("SELECT
bc_produits.quantite,
bc_produits.bc_produit_pu,
bcs.id_bc,
bcs.montant,
bcs.tva
FROM
bcs
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
WHERE
bcs.id_fournisseur_fk = $id_fourn AND
bcs.numero = $numero
 ");

return $query->result();
     

	
	}

      

          public function modifier_bc($id_bc,$numero,$date)
	{
	$query = $this->db->query("UPDATE `bcs` SET `date`='$date', `numero`='$numero' WHERE (`id_bc`='$id_bc')

 ");

	
	}


}


?>